-- @owner: ningyali
-- @date: 2024-08-12
-- @testpoint: count(column) column列存在主键约束

--step1:创建测试表; expect: 执行成功
drop table if exists t_ustore_count_optim_0001;
create table t_ustore_count_optim_0001(
	c_id int , c_int int not null, c_integer integer primary key,
	c_bool bool, c_boolean boolean, c_bigint bigint,
	c_real real, c_double float8,c_decimal decimal(38),
	c_number number(38), c_numeric numeric(38),
	c_char char(200), c_varchar varchar(20), c_varchar2 varchar2(4000),
	c_date date, c_datetime date, c_timestamp timestamp not null
	)  with (storage_type=ustore);

--step2:创建序列; expect: 执行成功
drop sequence if exists seq_ustore_count_winfun_0001;
create sequence seq_ustore_count_winfun_0001 increment by 1 start with 100000;

--step3:向表中插入数据; expect: 执行成功
begin
	for i in 1..3000 loop
	insert into t_ustore_count_optim_0001 values(seq_ustore_count_winfun_0001.nextval,i,i+454,'true','false',i+2.3e7,
i/7,i+4.9690546872 , -9.59229875565,8.76779041557e+7+(i*2),7.889845861e+17 +1-i,
lpad('ee2t2xnbzzkih',80,'etskbfw@p%my8tesjbemus6gnv3'),'ci@k_gnqvk','txrl(on!kccx~op(y4um8n*t2)@'||i||'h@atfy7skq%tw0ud+tb*crm=x',
to_timestamp('2019-01-04 16:33:47.123456','yyyy-mm-dd hh24:mi:ss.ffffff'),to_timestamp('2019-01-04 16:33:47.123456','yyyy-mm-dd hh24:mi:ss.ffffff'),to_timestamp('2019-01-04 16:33:47.123456','yyyy-mm-dd hh24:mi:ss.ffffff'));
    end loop;
end;
/

--step4: count(column) column列存在主键约束; expect: 执行成功
select c_date,count(c_integer) from t_ustore_count_optim_0001
group by c_date order by 1,2 desc;
explain analyze select c_date,count(c_integer) from t_ustore_count_optim_0001
group by c_date order by 1,2 desc;

--step5:清理环境; expect: 执行成功
drop table if exists t_ustore_count_optim_0001;
drop sequence if exists seq_ustore_count_winfun_0001;
